﻿Imports System.Data.OleDb
Imports ExportExcel
Imports Aspose.Cells
Namespace QuanLyKhachHang
    ''' <summary>
    '''     
    ''' </summary>
    ''' <remarks>
    '''     
    ''' </remarks>
    Public Class frmDanhMucKhachHang
        Private RowEditIndex As Integer = 0
        Private AddNewFlag As Boolean = True
        Private KhachHangId_Edit As Integer = -1
        Private isSearch As Boolean = False
#Region "Form Event"
        Private Sub frmDanhMucKhachHang_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            Try
                'Control.CheckForIllegalCrossThreadCalls = False
                'LoadData_Form()
                'DanhMucKhachHang_Load(0, 20)
                DanhMucKhachHang_GetItems()
                TinhThanh_Load()
                cmdPrev.Enabled = False
                'Phân quyền Form
                Dim aPermission(4) As Boolean
                aPermission = PhanQuyenForm("MenuKhachHang", frmMain.UserId)
                cmdAddNew.Enabled = aPermission(0)
                cmdEdit.Enabled = aPermission(1)
                cmdDelete.Enabled = aPermission(2)
                cmdPrint.Enabled = aPermission(3)

            Catch ex As Exception
                MessageBox.Show(ex.ToString())
            End Try
        End Sub
        Private iBegin As Integer = 0
        Private iEnd As Integer = 20
        Private iSize As Integer = 20
        Private Sub cmdPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdPrev.Click
            Try
                iEnd = iBegin - 1
                iBegin = iBegin - iSize
                DanhMucKhachHang_Load(iBegin, iEnd)
            Catch ex As Exception

            End Try
        End Sub

        Private Sub cmdNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdNext.Click
            Try
                iBegin = iEnd + 1
                iEnd = iEnd + iSize
                DanhMucKhachHang_Load(iBegin, iEnd)
            Catch ex As Exception

            End Try
        End Sub
#End Region
#Region "BackgroundWorking Event"
        Private Sub bgWorker_DoWork(ByVal sender As Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles bgWorker.DoWork
            'e.Result = DanhMucKhachHang_Load()
        End Sub

        Private Sub bgWorker_ProgressChanged(ByVal sender As Object, ByVal e As System.ComponentModel.ProgressChangedEventArgs) Handles bgWorker.ProgressChanged

        End Sub

        Private Sub bgWorker_RunWorkerCompleted(ByVal sender As Object, ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles bgWorker.RunWorkerCompleted
            Try
                'dgvKhachHang.AutoGenerateColumns = False
                'dgvKhachHang.DataSource = dtDanhMucKhachHang
                'Panel_BackgroundWorking.Visible = False
                'Panel_ButtonFunction.Enabled = True
                'isSearch = False
            Catch ex As Exception

            End Try
        End Sub
#End Region
#Region "Button Event"
        Private Sub dgvKhachHang_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvKhachHang.CellClick
            Try
                RowEditIndex = e.RowIndex
            Catch ex As Exception
                ShowError()
            End Try
        End Sub
        Private Sub cmdAddNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAddNew.Click
            Try
                frmThemKhachHang.KhachHangId = -1
                ShowModalForm(frmThemKhachHang, False, False)
                frmThemKhachHang.KhachHangId = -1
                DanhMucKhachHang_Load(iBegin, iEnd)
            Catch ex As Exception
                ShowError("Có lỗi trong quá trình xử lý.Xin vui lòng thực hiện lại", MessageBoxIcon.Error)
            End Try
        End Sub

        Private Sub cmdEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdEdit.Click
            Try
                KhachHangId_Edit = dgvKhachHang.Rows(dgvKhachHang.CurrentCell.RowIndex).Cells("KhachHangId").Value
                frmThemKhachHang.KhachHangId = KhachHangId_Edit
                ShowModalForm(frmThemKhachHang, False, False)
                frmThemKhachHang.KhachHangId = -1
                DanhMucKhachHang_Load(iBegin, iEnd)
            Catch ex As Exception
                ShowError("Có lỗi trong quá trình xử lý.Xin vui lòng thực hiện lại", MessageBoxIcon.Error)
            End Try
        End Sub

        Private Sub cmdDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDelete.Click
            Try
                Dim DBO_DMKhachHang As New DanhMucKhachHang
                Dim TenKhachHang As String
                KhachHangId_Edit = dgvKhachHang.Rows(dgvKhachHang.CurrentCell.RowIndex).Cells("KhachHangId").Value
                TenKhachHang = dgvKhachHang.Rows(RowEditIndex).Cells("TenKhachHang").Value
                Dim bResult As Boolean
                Dim dlgRes As DialogResult
                dlgRes = MessageBox.Show( _
                      "Bạn muốn xóa thông tin khách hàng : " & TenKhachHang, _
                      "Xóa loại sản phẩm", _
                MessageBoxButtons.OKCancel, _
                      MessageBoxIcon.Question)
                If dlgRes = DialogResult.OK Then
                    bResult = DBO_DMKhachHang.Xoa(KhachHangId_Edit, TenKhachHang)
                    If bResult Then
                        DanhMucKhachHang_Load(iBegin, iEnd)
                    End If
                End If

            Catch ex As Exception
                ShowError("Có lỗi trong quá trình xử lý.Xin vui lòng thực hiện lại")
            End Try
        End Sub

        Private Sub cmdPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdPrint.Click
            Try
                frmInDanhMucKhachHang.TenKhachHang = txtTextSearch.Text
                If filterKhuVuc.Checked Then
                    frmInDanhMucKhachHang.MaTinhThanh = cbxTinhThanh.SelectedValue
                Else
                    frmInDanhMucKhachHang.MaTinhThanh = 0
                End If
                frmInDanhMucKhachHang.Show()
            Catch ex As Exception
                ShowError("Có lỗi trong quá trình xử lý.Xin vui lòng thực hiện lại")
            End Try
        End Sub

        Private Sub cmdConvertWord_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
            Try

            Catch ex As Exception
                ShowError("Có lỗi trong quá trình xử lý.Xin vui lòng thực hiện lại")
            End Try
        End Sub

        Private Sub cmdConvertExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
            Try

            Catch ex As Exception
                ShowError("Có lỗi trong quá trình xử lý.Xin vui lòng thực hiện lại")
            End Try
        End Sub
#End Region
#Region "Load Data"
        Public Sub LoadData_Form()
            Try
                'dtDanhMucKhachHang = New DataTable
                'Panel_BackgroundWorking.Visible = True
                'Panel_ButtonFunction.Enabled = False
                'bgWorker.WorkerReportsProgress = True
                'bgWorker.WorkerSupportsCancellation = True
                'bgWorker.RunWorkerAsync()
            Catch ex As Exception

            End Try
        End Sub
        Private Sub TinhThanh_Load()
            Try
                Dim dbConn As New OleDb.OleDbConnection
                Dim dataAccess As New DataAccess
                Dim dsTinhThanh As New DataSet
                Dim dtTinhThanh As New DataTable
                Dim query As String = "Select TinhThanhId,TenTinhThanh From TinhThanh Order By TenTinhThanh ASC"
                Dim adapter As OleDb.OleDbDataAdapter
                dataAccess.ConnectDatabase_DBAccess(dbConn)
                adapter = New OleDb.OleDbDataAdapter(query, dbConn)
                adapter.Fill(dsTinhThanh)
                dbConn.Close()
                dtTinhThanh = dsTinhThanh.Tables(0)
                cbxTinhThanh.DataSource = dtTinhThanh
                cbxTinhThanh.DisplayMember = "TenTinhThanh"
                cbxTinhThanh.ValueMember = "TinhThanhId"
            Catch ex As Exception
                ShowError()
            End Try
        End Sub
#End Region
        ''' <summary>
        '''     
        ''' </summary>
        ''' <returns>
        '''     A Object value...
        ''' </returns>
        Private Sub DanhMucKhachHang_GetItems()
            Try
                Cursor.Current = Cursors.WaitCursor
                Dim controller As New DanhMucKhachHangBLL
                Dim listKH As New List(Of KhachHang)
                If filterKhuVuc.Checked Then
                    listKH = controller.DanhMucKhachHang_GetItems(txtTextSearch.Text, cbxTinhThanh.SelectedValue)
                Else
                    listKH = controller.DanhMucKhachHang_GetItems(txtTextSearch.Text, 0)
                End If
                dgvKhachHang.AutoGenerateColumns = False
                dgvKhachHang.DataSource = listKH
                Cursor.Current = Cursors.Default
            Catch ex As Exception
                Throw ex
            End Try
        End Sub
        Private Function DanhMucKhachHang_Load(ByVal iBegin As Integer, ByVal iEnd As Integer) As Boolean
            Try
                Dim dbConn As New OleDb.OleDbConnection
                Dim dataAccess As New DataAccess
                Dim dsKhachHang As New DataSet
                Dim dtKhachHang As New DataTable
                Dim query As String
                If txtTextSearch.Text <> "" Then
                    If filterKhuVuc.Checked Then
                        query = "SELECT  * FROM (SELECT ROW_NUMBER() OVER (ORDER BY TenKhachHang ASC) AS Row, * FROM DanhMucKhachHang Where TenKhachHang Like N'%" & txtTextSearch.Text & "%' And MaTinhThanh=" & cbxTinhThanh.SelectedValue & ") AS KhachHangPaging WHERE  Row >=" & iBegin & " AND Row <=" & iEnd & " And Xoa=0  Order By MaKhachHang ASC"
                    Else
                        query = "SELECT  * FROM (SELECT  ROW_NUMBER() OVER (ORDER BY TenKhachHang ASC) AS Row, * FROM DanhMucKhachHang Where TenKhachHang Like N'%" & txtTextSearch.Text & "%') AS KhachHangPaging WHERE  Row >=" & iBegin & " AND Row <=" & iEnd & " And Xoa=0  Order By MaKhachHang ASC"
                    End If
                Else
                    query = "SELECT  * FROM (SELECT  ROW_NUMBER() OVER (ORDER BY TenKhachHang ASC) AS Row, * FROM DanhMucKhachHang) AS KhachHangPaging WHERE  Row >=" & iBegin & " AND Row <=" & iEnd & " And Xoa=0  Order By MaKhachHang ASC"
                End If
                Dim adapter As OleDb.OleDbDataAdapter
                dataAccess.ConnectDatabase_DBAccess(dbConn)
                adapter = New OleDb.OleDbDataAdapter(query, dbConn)
                adapter.Fill(dsKhachHang)
                dbConn.Close()
                dtKhachHang = dsKhachHang.Tables(0)
                dgvKhachHang.AutoGenerateColumns = False
                'tao so thu tu
                dtKhachHang.Columns.Add("STT")
                For i = 0 To dtKhachHang.Rows.Count - 1
                    If iBegin = 0 Then
                        dtKhachHang.Rows(i)("STT") = i + iBegin + 1
                    Else
                        dtKhachHang.Rows(i)("STT") = i + iBegin
                    End If

                Next
                dgvKhachHang.AutoGenerateColumns = False
                dgvKhachHang.DataSource = dtKhachHang
                isSearch = False
                If iBegin = 0 Then
                    cmdPrev.Enabled = False
                Else
                    cmdPrev.Enabled = True
                End If
                'clone schema 0|1|2|3|4|5|6|7|8--->20  iBegin=20--40
                'dtDanhMucKhachHang = dtKhachHang.Clone()
                'For i As Integer = 0 To dtKhachHang.Rows.Count - 1
                '    dtKhachHang.Rows(i)("STT") = i + 1
                '    'copyrow
                '    dtDanhMucKhachHang.ImportRow(dtKhachHang.Rows(i))
                '    bgWorker.ReportProgress(i / (5000 * 100))
                '    Threading.Thread.Sleep(1)
                'Next
                'Return dtDanhMucKhachHang
            Catch ex As Exception
                MessageBox.Show(ex.ToString())
            End Try
        End Function
        Private Sub cmdLuu_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
            'Try
            '    If txtTenKhachHang.Text = "" Then
            '        lblEmptyTenKhachHang.Visible = True
            '        Exit Sub
            '    End If
            '    lblEmptyTenKhachHang.Visible = False
            '    lblEmptySoDienThoai.Visible = False
            '    lblEmptyDiaChi.Visible = False
            '    Dim MaKhachHang As String = ""
            '    Dim dataAccess As New DataAccess
            '    Dim dbConn As New OleDb.OleDbConnection
            '    Dim dbCommand As OleDbCommand
            '    Dim query As String
            '    dataAccess.ConnectDatabase_DBAccess(dbConn)
            '    If AddNewFlag Then
            '        'Tạo mã khách hàng
            '        query = "Select Max(KhachHangId) From DanhMucKhachHang"
            '        dbCommand = New OleDbCommand(query, dbConn)
            '        Dim KhachHangId As Integer
            '        If IsDBNull(dbCommand.ExecuteScalar()) Then
            '            KhachHangId = 1
            '        Else
            '            KhachHangId = Integer.Parse(dbCommand.ExecuteScalar()) + 1
            '        End If
            '        Select Case KhachHangId
            '            Case Is <= 9
            '                MaKhachHang = "KH000" & KhachHangId
            '            Case Is <= 99
            '                MaKhachHang = "KH00" & KhachHangId
            '            Case Is <= 999
            '                MaKhachHang = "KH0" & KhachHangId
            '            Case Is <= 9999
            '                MaKhachHang = "KH" & KhachHangId
            '            Case Else
            '                MaKhachHang = "KHEx" & KhachHangId
            '        End Select
            '        'end
            '        'luu thong tin khach hang
            '        query = String.Format("Insert Into DanhMucKhachHang(MaKhachHang,TenKhachHang,SoDienThoai,DiaChi) Values(N'{0}',N'{1}',N'{2}',N'{3}')", MaKhachHang, txtTenKhachHang.Text, txtSoDienThoai.Text, txtDiaChi.Text)
            '        dbCommand = New OleDbCommand(query, dbConn)
            '        dbCommand.ExecuteNonQuery()
            '    Else
            '        query = String.Format("Update DanhMucKhachHang Set TenKhachHang=N'{0}',SoDienThoai=N'{1}',DiaChi=N'{2}' Where KhachHangId={3}", txtTenKhachHang.Text, txtSoDienThoai.Text, txtDiaChi.Text, KhachHangId_Edit)
            '        dbCommand = New OleDbCommand(query, dbConn)
            '        dbCommand.ExecuteNonQuery()
            '        KhachHangId_Edit = -1
            '    End If
            '    dbConn.Close()
            '    'clear Info
            '    txtTenKhachHang.Text = ""
            '    txtSoDienThoai.Text = ""
            '    txtDiaChi.Text = ""
            '    AddNewFlag = True
            '    KhachHang_Load()
            'Catch ex As Exception
            '    MessageBox.Show(ex.ToString())
            'End Try
        End Sub



        Private Sub Sua_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Sua.Click
            'Try
            '    Dim KhachHang As String
            '    Dim SoDienThoai As String
            '    Dim DiaChi As String
            '    If RowEditIndex >= 0 Then
            '        KhachHangId_Edit = Integer.Parse(dgvKhachHang.Rows(RowEditIndex).Cells("KhachHangId").Value.ToString)
            '        KhachHang = dgvKhachHang.Rows(RowEditIndex).Cells("TenKhachHang").Value.ToString
            '        SoDienThoai = dgvKhachHang.Rows(RowEditIndex).Cells("SoDienThoai").Value.ToString
            '        DiaChi = dgvKhachHang.Rows(RowEditIndex).Cells("DiaChi").Value.ToString
            '        txtTenKhachHang.Text = KhachHang
            '        txtSoDienThoai.Text = SoDienThoai
            '        txtDiaChi.Text = DiaChi
            '        AddNewFlag = False
            '    End If
            '    'refresh RowEditIndex
            '    RowEditIndex = -1
            'Catch ex As Exception

            'End Try
        End Sub

        Private Sub Xoa_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Xoa.Click
            'Try
            '    If RowEditIndex >= 0 Then
            '        Dim KhachHangId As Integer
            '        Dim TenKhachHang As String
            '        KhachHangId = dgvKhachHang.Rows(RowEditIndex).Cells("KhachHangId").Value.ToString
            '        TenKhachHang = dgvKhachHang.Rows(RowEditIndex).Cells("TenKhachHang").Value.ToString
            '        Dim dlgRes As DialogResult
            '        dlgRes = MessageBox.Show( _
            '      "Bạn muốn xóa khách hàng: " & TenKhachHang, _
            '      "Thông báo", _
            'MessageBoxButtons.OKCancel, _
            '      MessageBoxIcon.Question)
            '        If dlgRes = Windows.Forms.DialogResult.OK Then
            '            Dim dataAccess As New DataAccess
            '            Dim dbConn As New OleDb.OleDbConnection
            '            Dim dbCommand As OleDbCommand
            '            Dim query As String
            '            dataAccess.ConnectDatabase_DBAccess(dbConn)
            '            query = "Delete From DanhMucKhachHang Where KhachHangId=" & KhachHangId
            '            dbCommand = New OleDbCommand(query, dbConn)
            '            dbCommand.ExecuteNonQuery()
            '            dbConn.Close()
            '            KhachHang_Load()
            '        End If
            '    End If
            '    'refresh RowEditIndex
            '    RowEditIndex = -1
            'Catch ex As Exception

            'End Try
        End Sub

        Private Sub filterKhuVuc_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles filterKhuVuc.CheckedChanged
            Try
                If filterKhuVuc.Checked Then
                    cbxTinhThanh.Visible = True
                Else
                    cbxTinhThanh.Visible = False
                End If
            Catch ex As Exception

            End Try
        End Sub

        Private Sub cmdSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSearch.Click
            Try
                DanhMucKhachHang_GetItems()
            Catch ex As Exception

            End Try
        End Sub

        Private Sub txtTextSearch_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtTextSearch.KeyUp
            Try
                If e.KeyCode = Keys.Enter Then
                    isSearch = True
                    '                LoadData_Form()
                    iBegin = 0
                    iEnd = 20
                    DanhMucKhachHang_Load(iBegin, iEnd)
                End If
            Catch ex As Exception

            End Try
        End Sub


        Private Sub cemConvertExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cemConvertExcel.Click
            Try
                frmSaveFile.FileType = ".xls"
                ShowModalForm(frmSaveFile, False, False)
                Dim result As Boolean
                result = frmSaveFile.Result
                If result Then
                    Dim dsKhachHang As New DataSet
                    Dim dtDanhMucKhachHang As DataTable
                    Dim controller As New DanhMucKhachHangBLL
                    If filterKhuVuc.Checked Then
                        dtDanhMucKhachHang = controller.DanhMucKhachHang_GetItems_ToDataTable(txtTextSearch.Text, cbxTinhThanh.SelectedValue)
                    Else
                        dtDanhMucKhachHang = controller.DanhMucKhachHang_GetItems_ToDataTable(txtTextSearch.Text, 0)
                    End If
                    dsKhachHang.Tables.Add(dtDanhMucKhachHang)
                    dtDanhMucKhachHang.Columns.RemoveAt(0)
                    dtDanhMucKhachHang.Columns.RemoveAt(0)
                    Dim Path As String = frmSaveFile.Path
                    Dim FileName As String = frmSaveFile.FileName
                    'Import Data from a  DataTable
                    Dim workbook As Workbook = New Workbook()
                    Dim sheet As Worksheet = workbook.Worksheets(0)
                    ImportDataTable(sheet, dtDanhMucKhachHang)
                    workbook.Save(Path & "/" & FileName & ".xls")
                End If
                frmSaveFile.txtPath.Text = ""
                frmSaveFile.txtFile.Text = ""
            Catch ex As Exception

            End Try
        End Sub
        Private Sub ImportDataTable(ByVal sheet As Worksheet, ByVal dataSource As DataTable)
            sheet.Cells("A3").PutValue("MÃ KHÁCH HÀNG")
            sheet.Cells("A3").Style.Font.IsBold = True
            sheet.Cells("B3").PutValue("TÊN KHÁCH HÀNG")
            sheet.Cells("B3").Style.Font.IsBold = True
            sheet.Cells("C3").PutValue("SỐ ĐIỆN THOẠI")
            sheet.Cells("C3").Style.Font.IsBold = True
            sheet.Cells("D3").PutValue("FAX")
            sheet.Cells("D3").Style.Font.IsBold = True
            sheet.Cells("E3").PutValue("ĐỊA CHỈ")
            sheet.Cells("E3").Style.Font.IsBold = True
            sheet.Cells("F3").PutValue("KHU VỰC")
            sheet.Cells("F3").Style.Font.IsBold = True
            sheet.Cells("G3").PutValue("MÃ SỐ THUẾ")
            sheet.Cells("G3").Style.Font.IsBold = True
            sheet.Cells("H3").PutValue("SỐ TÀI KHOẢN")
            sheet.Cells("H3").Style.Font.IsBold = True
            sheet.Cells("I3").PutValue("NGÂN HÀNG")
            sheet.Cells("I3").Style.Font.IsBold = True
            sheet.Cells("J3").PutValue("GHI CHÚ")
            sheet.Cells("J3").Style.Font.IsBold = True
            sheet.Cells.ImportDataTable(dataSource, False, "A4")
            'Autofit Sheet Columns
            sheet.AutoFitColumns()
            sheet.AutoFilter.Range = "A3:J3"

        End Sub
    End Class
End Namespace